package com.xnx3.wangmarket.admin.service.impl;

import com.xnx3.j2ee.dao.SqlDAO;
import com.xnx3.j2ee.entity.User;
import com.xnx3.j2ee.service.SqlService;
import com.xnx3.j2ee.vo.BaseVO;
import com.xnx3.wangmarket.admin.entity.*;
import com.xnx3.wangmarket.admin.service.CompanyInfoService;
import com.xnx3.wangmarket.admin.vo.CompanyInfoVo;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.management.Query;
import javax.servlet.http.HttpServletRequest;
import java.util.*;

import static com.xnx3.j2ee.shiro.ShiroFunc.getUserId;

@Service("companyInfoService")
public class CompanyInfoImpl implements CompanyInfoService {
    @Resource
    private SqlDAO sqlDAO;

    @Override
    public List<CompanyInfo> queryCompanyInfo(int userid) {
        return sqlDAO.findBySqlQuery("select * from Company_Info where user_id=" + userid, CompanyInfo.class);
    }

    @Override
    public void updateCompanyInfo(CompanyInfo companyInfo, int userid, HttpServletRequest request) {
        sqlDAO.save(companyInfo);
    }

    @Override
    public void delCompanyInfo(CompanyInfo companyInfo) {
        sqlDAO.delete(companyInfo);
    }

    @Override
    public List<Product> queryProductInfo(int userid) {
        return sqlDAO.findBySqlQuery("select * from product where user_id=" + userid + "order by timestamps desc limit 1", Product.class);
    }

    @Override
    public List<News> queryNewList(int userid, Integer id) {
        return sqlDAO.findBySqlQuery("SELECT * FROM news where userid = " + userid + " and cid=" + id, News.class);
    }

    @Override
    public List<Product> queryProduct(int userid) {
        return sqlDAO.findBySqlQuery("SELECT  *  FROM   product  where user_id =" + userid + " ORDER BY  TIMESTAMPs  desc  LIMIT   4", Product.class);
    }

    @Override
    public List<News> queryNews(int userid, int cid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `news` where userid = " + userid + " and cid=" + cid + " and type=1 ORDER BY addtime DESC LIMIT 5", News.class);
    }

    @Override
    public List<News> queryAllNews(int userid, int cid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `news` where userid = " + userid + " and cid = " + cid + " and type=1 ORDER BY addtime DESC LIMIT 5", News.class);
    }

    @Override
    public List<NewsData> queryNewsData(int id) {
        return sqlDAO.findBySqlQuery("select * from news_data where id =" + id, NewsData.class);
    }

    @Override
    public List<SiteColumn> queryNewsOneColumn(int userid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `site_column` where userid=" + userid + " and type=1", SiteColumn.class);
    }

    @Override
    public List<SiteColumn> queryNewsTwoColumn(int parentid, int userid) {
        return sqlDAO.findBySqlQuery("select * from site_column where parentid = " + parentid + " and userid = " + userid, SiteColumn.class);
    }

    @Override
    public List<EmpiricalConclusion> queryEmpirical(int userid) {
        return sqlDAO.findBySqlQuery("SELECT  *  FROM   empirical_conclusion  where user_id =" + userid + " ORDER BY  TIMESTAMPs  desc LIMIT   4", EmpiricalConclusion.class);
    }

    @Override
    public List<Product> twoSelProduct(int siteColumnId, int userid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `product` where site_column_id = " + siteColumnId + " and user_id = " + userid, Product.class);
    }

    @Override
    public List<EmpiricalConclusion> twoSelEmpircal(int siteColumnId, int userid) {
        return sqlDAO.findBySqlQuery("SELECT * FROM `empirical_conclusion` where site_column_id = " + siteColumnId + " and user_id = " + userid, EmpiricalConclusion.class);
    }

    @Override
    public List<Product> allProduct() {
        return sqlDAO.findBySqlQuery("SELECT  *  FROM   product  ORDER BY  TIMESTAMPs  desc  LIMIT   4", Product.class);
    }

    @Override
    public List<Site> querySite(int userId) {
        return sqlDAO.findBySqlQuery("select * from site where userId = " + userId, Site.class);
    }

    @Override
    public List<Site> queryCountNum() {
        List<Site> site = sqlDAO.findBySqlQuery("select a.*,c.title,c.titlepic,c.intro from site a \n" +
                "LEFT JOIN site_column b on a.id = b.siteid and b.type='3' and b.`name`  in('公司简介','基本信息') \n" +
                "INNER JOIN news c on a.id = c.siteid and b.id = c.cid ORDER BY a.count_Num desc", Site.class);
        List<News> news = sqlDAO.findBySqlQuery("select c.* from site a \n" +
                "LEFT JOIN site_column b on a.id = b.siteid and b.type='3' and b.`name`  in('公司简介','基本信息') \n" +
                "INNER JOIN news c on a.id = c.siteid and b.id = c.cid ORDER BY a.count_Num desc", News.class);
        List<User> users = sqlDAO.findBySqlQuery("select * from user where id in(\n" +
                "select c.userid from site a \n" +
                "LEFT JOIN site_column b on a.id = b.siteid and b.type='3' and b.`name`  in('公司简介','基本信息') \n" +
                "INNER JOIN news c on a.id = c.siteid and b.id = c.cid ORDER BY a.count_Num)\n", User.class);

        List<Site> list = new ArrayList<>();
        for (Site st : site) {
            for (News ns : news) {
                if (st.getId().equals(ns.getSiteid())) {
                    st.setIntro(ns.getIntro());
                    st.setTitlepic(ns.getTitlepic());
                    st.setTitle(ns.getTitle());
                    for (User user : users) {
                        if (user.getId().equals(st.getUserid())) {
                            st.setRoleCode(user.getRoleCode());
                            if (user.getRoleCode() == null || user.getRoleCode().equals("QY")) {
                                st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("ZMXC")) {
                                st.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("ZJ")) {
                                st.setUrl("http://118.25.235.213:8030/zj/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("HZS")) {
                                st.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("ZZDH")) {
                                st.setUrl("http://118.25.235.213:8030/zjdh/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("GXS")) {
                                st.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("ZJYY")) {
                                st.setUrl("http://118.25.235.213:8030/zjyy/#/mains/" + user.getId());
                            } else if (user.getRoleCode().equals("JD")) {
                                st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());

                            }
                        }
                    }
                    list.add(st);
                }
            }
        }

        Collections.sort(list);
        return list;
    }

    @Override
    public void updateCountNum(int userId, int count) {
        sqlDAO.executeSql("update site  set count_Num=" + count + " where userId=" + userId);
    }

    @Override
    public List<Site> queryNum(int userId) {
        return sqlDAO.findBySqlQuery("select * from site where userid=" + userId, Site.class);
    }

    @Override
    public List<News> queryNews(int page, int count, News news) {
        return sqlDAO.findBySqlQuery("select t.* from news t join (\n" +
                "select b.id id from user a join site b on a.id = b.userid where a.role_code='ZMXC') w on t.siteid = w.id\n" +
                "GROUP BY t.userid limit " + ((page - 1) * 10) + "," + count, News.class);
    }

    @Override
    public Page findNewsPage(int page, int count, News news) {

        List<News> pctList = queryNews(page, count, news);
        int totle = pctList.size();
        Page p = new Page();
        p.setNewsList(pctList);
        p.setCurrentPage(page);
        p.setCount(count);
        p.setTotalCount(totle);
        int totlePage = totle % count == 0 ? totle / count : (totle / count) + 1;
        p.setTotalPage(totlePage);

        return p;
    }

    @Override
    public Map<String,Object> getServiceDemonstration(String areaCode) {

        List<Site> sites = sqlDAO.findBySqlQuery("SELECT c.* FROM USER a LEFT JOIN USER b ON a.role_code = b.role_code\n" +
                " AND a.id <= b.id LEFT JOIN site c ON a.id = c.userid LEFT JOIN site_column d ON c.id = d.siteid \n" +
                "AND a.id = d.userid AND d.type = '3' and d.`name`  in('公司简介','基本信息')  LEFT JOIN news e ON e.userid = a.id AND e.siteid = c.id AND \n" +
                "e.cid = d.id WHERE a.area_code LIKE '" + areaCode + "%' AND A.role_code IS NOT NULL GROUP BY a.role_code, a.id HAVING count(b.area_code) <= 3", Site.class);
        List<News> news = sqlDAO.findBySqlQuery("SELECT e.* FROM USER a LEFT JOIN USER\n" +
                " b ON a.role_code = b.role_code AND a.id <= b.id LEFT JOIN site c ON a.id = c.userid \n" +
                "LEFT JOIN site_column d ON c.id = d.siteid AND a.id = d.userid AND d.type = '3' and  d.`name`  in('公司简介','基本信息')  LEFT JOIN \n" +
                "news e ON e.userid = a.id AND e.siteid = c.id AND e.cid = d.id WHERE a.area_code LIKE '" + areaCode + "%'\n" +
                " AND A.role_code IS NOT NULL GROUP BY a.role_code, a.id HAVING count(b.area_code) <= 3", News.class);
        List<User> users = sqlDAO.findBySqlQuery("SELECT a.*, \n" +
                "( CASE a.role_code WHEN 'QY' THEN '企业' WHEN 'ZMXC' THEN '最美乡村' \n" +
                "WHEN 'ZZDH' THEN '种植大户' WHEN 'ZJ' THEN '专家' WHEN 'GXS' THEN '供销社' WHEN 'JD' THEN '基地' END ) \n" +
                "roleName FROM USER a LEFT JOIN USER b ON a.role_code = b.role_code AND a.id <= b.id LEFT JOIN site c ON \n" +
                "a.id = c.userid LEFT JOIN site_column d ON c.id = d.siteid AND a.id = d.userid AND d.type = '3' and  d.`name`  in('公司简介','基本信息')  LEFT JOIN news e \n" +
                "ON e.userid = a.id AND e.siteid = c.id AND e.cid = d.id WHERE a.area_code LIKE '" + areaCode + "%' AND A.role_code IS NOT NULL \n" +
                "GROUP BY a.role_code, a.id HAVING count(b.area_code) <= 3", User.class);

Map<String,Object> map = new HashMap<>();

        List<Site> list = new ArrayList<>();
        List<Site> list1 = new ArrayList<>();
        List<Site> list2 = new ArrayList<>();
        List<Site> list3 = new ArrayList<>();
        List<Site> list4 = new ArrayList<>();
        List<Site> list5 = new ArrayList<>();
        List<Site> list6 = new ArrayList<>();

        for (Site st : sites) {
            for (News ns : news) {
                if (ns != null && st != null) {

                    if (st.getId().equals(ns.getSiteid())) {
                        st.setIntro(ns.getIntro());
                        st.setTitlepic(ns.getTitlepic());
                        st.setTitle(ns.getTitle());
                        for (User user : users) {
                            if (user.getId().equals(st.getUserid())) {
                                st.setRoleCode(user.getRoleCode());
                                if (user.getRoleCode() == null || user.getRoleCode().equals("QY")) {
                                    st.setRoleName("企业");
                                    st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());
                                    list.add(st);
                                } else if (user.getRoleCode().equals("ZMXC")) {
                                    st.setRoleName("最美乡村");
                                    st.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + user.getId());
                                    list1.add(st);

                                } else if (user.getRoleCode().equals("ZJ")) {
                                    st.setRoleName("专家");
                                    st.setUrl("http://118.25.235.213:8030/zj/#/mains/" + user.getId());
                                    list2.add(st);
                                } else if (user.getRoleCode().equals("HZS")) {
                                    st.setRoleName("合作社");
                                    st.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + user.getId());
                                    list3.add(st);
                                } else if (user.getRoleCode().equals("ZZDH")) {
                                    st.setRoleName("种植大户");
                                    st.setUrl("http://118.25.235.213:8030/zjdh/#/mains/" + user.getId());
                                    list4.add(st);
                                } else if (user.getRoleCode().equals("GXS")) {
                                    st.setRoleName("供销社");
                                    st.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + user.getId());
                                    list5.add(st);
                                } else if (user.getRoleCode().equals("ZJYY")) {
                                    st.setUrl("http://118.25.235.213:8030/zjyy/#/mains/" + user.getId());
                                } else if (user.getRoleCode().equals("JD")) {
                                    st.setRoleName("基地");
                                    st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());
                                    list6.add(st);
                                }
                            }
                        }
                    }
                    map.put("企业",list);
                    map.put("最美乡村",list1);
                    map.put("专家",list2);
                    map.put("合作社",list3);
                    map.put("种植大户",list4);
                    map.put("供销社",list5);
                    map.put("基地",list6);
                }
            }
        }

//        Collections.sort(list);
        return map;
    }

    @Override
    public List<Site> getPageServiceDemonstration(Site site, int page, int num) {

        List<Site> sites = sqlDAO.findBySqlQuery("SELECT c.* FROM USER a LEFT JOIN USER b ON a.role_code = b.role_code AND a.id <= b.id LEFT JOIN site c\n" +
                " ON a.id = c.userid LEFT JOIN site_column d ON c.id = d.siteid AND a.id = d.userid AND d.type = '3' and  d.`name`  in('公司简介','基本信息')  LEFT JOIN\n" +
                " news e ON e.userid = a.id AND e.siteid = c.id AND e.cid = d.id WHERE a.area_code LIKE '"+site.getAreaCode()+"%' AND\n" +
                " a.role_code = '"+site.getRoleCode()+"' AND A.role_code IS NOT NULL GROUP BY a.role_code, a.id LIMIT "+page+"," +num,Site.class);
        List<News> news = sqlDAO.findBySqlQuery("SELECT e.* FROM USER a LEFT JOIN USER b ON a.role_code = b.role_code AND a.id <= b.id LEFT JOIN site c\n" +
                " ON a.id = c.userid LEFT JOIN site_column d ON c.id = d.siteid AND a.id = d.userid AND d.type = '3' and  d.`name`  in('公司简介','基本信息')  LEFT JOIN\n" +
                " news e ON e.userid = a.id AND e.siteid = c.id AND e.cid = d.id WHERE a.area_code LIKE '"+site.getAreaCode()+"%' AND\n" +
                " a.role_code = '"+site.getRoleCode()+"' AND A.role_code IS NOT NULL GROUP BY a.role_code, a.id LIMIT "+page+"," +num,News.class);
List<User> users = sqlDAO.findBySqlQuery("SELECT a.* FROM USER a LEFT JOIN USER b ON a.role_code = b.role_code AND a.id <= b.id LEFT JOIN site c\n" +
        " ON a.id = c.userid LEFT JOIN site_column d ON c.id = d.siteid AND a.id = d.userid AND d.type = '3' and  d.`name`  in('公司简介','基本信息')  LEFT JOIN\n" +
        " news e ON e.userid = a.id AND e.siteid = c.id AND e.cid = d.id WHERE a.area_code LIKE '"+site.getAreaCode()+"%' AND\n" +
        " a.role_code = '"+site.getRoleCode()+"' AND A.role_code IS NOT NULL GROUP BY a.role_code, a.id LIMIT "+page+"," +num,User.class);

        List<Site> list = new ArrayList<>();
        for (Site st : sites) {
            for (News ns : news) {
                if (ns != null && st != null) {

                    if (st.getId().equals(ns.getSiteid())) {
                        st.setIntro(ns.getIntro());
                        st.setTitlepic(ns.getTitlepic());
                        st.setTitle(ns.getTitle());
                        for (User user : users) {
                            if (user.getId().equals(st.getUserid())) {
                                st.setRoleCode(user.getRoleCode());
                                if (user.getRoleCode() == null || user.getRoleCode().equals("QY")) {
                                    st.setRoleName("企业");
                                    st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());

                                } else if (user.getRoleCode().equals("ZMXC")) {
                                    st.setRoleName("最美乡村");
                                    st.setUrl("http://118.25.235.213:8030/zmxc/#/mains/" + user.getId());

                                } else if (user.getRoleCode().equals("ZJ")) {
                                    st.setRoleName("专家");
                                    st.setUrl("http://118.25.235.213:8030/zj/#/mains/" + user.getId());

                                } else if (user.getRoleCode().equals("HZS")) {
                                    st.setRoleName("合作社");
                                    st.setUrl("http://118.25.235.213:8030/hzs/#/mains/" + user.getId());

                                } else if (user.getRoleCode().equals("ZZDH")) {
                                    st.setRoleName("种植大户");
                                    st.setUrl("http://118.25.235.213:8030/zjdh/#/mains/" + user.getId());
                                } else if (user.getRoleCode().equals("GXS")) {
                                    st.setRoleName("供销社");
                                    st.setUrl("http://118.25.235.213:8030/gxs/#/mains/" + user.getId());

                                } else if (user.getRoleCode().equals("ZJYY")) {
                                    st.setUrl("http://118.25.235.213:8030/zjyy/#/mains/" + user.getId());
                                } else if (user.getRoleCode().equals("JD")) {
                                    st.setRoleName("基地");
                                    st.setUrl("http://118.25.235.213:8030/rfpf/#/mains/" + user.getId());
                                }
                            }
                        }
                    }

                    list.add(st);
                }
            }
        }

        Collections.sort(list);
        return list;
    }

    @Override
    public Page findSitePage(Site site, int page, int num) {

        List<Site> pctList = getPageServiceDemonstration(site,page, num);
        int totle = pctList.size();
        Page p = new Page();
        p.setSiteList(pctList);
        p.setCurrentPage(page);
        p.setCount(num);
        p.setTotalCount(totle);
        int totlePage = totle % num == 0 ? totle / num : (totle / num) + 1;
        p.setTotalPage(totlePage);

        return p;
    }
}
